<?php
namespace app\common\command;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use think\Db;

class UserReport extends Command
{
    protected function configure()
    {
        $this->setName('user_stats')
            ->addArgument('action', Argument::OPTIONAL, "方法名称")
            ->addOption('start_date', null, Option::VALUE_REQUIRED, '开始日期')
            ->addOption('end_date', null, Option::VALUE_REQUIRED, '结束日期')
            ->setDescription('用户增长趋势数据汇总');
    }

    protected function execute(Input $input, Output $output)
    {

        $action = $input->getArgument('action');

        if (method_exists($this, $action)) {

            $start_date = $input->hasOption('start_date') ? $input->getOption('start_date') : date('Y-m-d', strtotime('-1days'));
            $end_date = $input->hasOption('end_date') ? $input->getOption('end_date') : date('Y-m-d', strtotime('-1days'));

            $this->$action($start_date, $end_date);
        }

    }

    /*用户增长趋势 - 投放号维度*/
    public function user_growth_daily_channel($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        //新增用户数汇总
        $sql = "SELECT 
                    source_channel_id as channel_id,COUNT(id) as number 
                FROM `quick_user` 
                WHERE regdate = ?
                GROUP by source_channel_id";

        //加卓用户数汇总
        $sql2 = "SELECT 
                    channel_id,COUNT(id) as number 
                FROM `quick_user_add_shortcut` 
                WHERE create_date = ?
                GROUP by channel_id";

        $date_rang = getDateByRange($start_date, $end_date);
        foreach ($date_rang as $v) {

            $list = Db::query($sql, [$v]);
            $list2 = Db::query($sql2, [$v]);
            $list2 = array_column($list2, null, 'channel_id');

            if($list){

                foreach ($list as $lv){

                    $record = Db::table('rp_user_growth_trend_channel')
                        ->where(['channel_id'=>$lv['channel_id'],'create_date'=>$v])
                        ->field('id')->find();

                    if($record){

                        Db::table('rp_user_growth_trend_channel')->where(['id'=>$record['id']])->update([
                            'new_user_num'       => $lv['number'],
                            'jiazhuo_user_num'   => isset($list2[$lv['channel_id']]['number']) ? $list2[$lv['channel_id']]['number'] : 0,
                        ]);

                    }else{

                        Db::table('rp_user_growth_trend_channel')->insert([
                            'new_user_num'       => $lv['number'],
                            'jiazhuo_user_num'   => isset($list2[$lv['channel_id']]['number']) ? $list2[$lv['channel_id']]['number'] : 0,
                            'channel_id'         => $lv['channel_id'],
                            'create_date'        => $v
                        ]);
                    }

                }

            }

        }

    }

    /*用户增长趋势 - 代理维度*/
    public function user_growth_daily_agent($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        $roleId = Db::table('cms_manager_role')->where('alias','agent')->value('id');
        $agents = Db::table('cms_manager_user')->where('role_id',$roleId)->column('id');

        foreach ($agents as $agent_id)
        {
            $channelIds = Db::table('cms_manager_user')->where('parent_id',$agent_id)->column('id');
            if(empty($channelIds))
            {
                continue;
            }

            $channelIdStr = implode(',',$channelIds);

            foreach ($date_rang as $v) {

                $sql = "SELECT 
                    create_date,SUM(new_user_num) as new_user_num,SUM(jiazhuo_user_num) as jiazhuo_user_num
                FROM `rp_user_growth_trend_channel` 
                WHERE create_date = '{$v}' AND channel_id in ({$channelIdStr})
                GROUP by create_date";

                $result = Db::query($sql);

                if($result){

                    $record = Db::table('rp_user_growth_trend_agent')
                        ->where(['create_date'=>$v,'agent_id'=>$agent_id])
                        ->field('id')->find();

                    if($record){

                        Db::table('rp_user_growth_trend_agent')->where(['id'=>$record['id']])->update([
                            'new_user_num'       => $result[0]['new_user_num'],
                            'jiazhuo_user_num'   => $result[0]['jiazhuo_user_num'],
                        ]);

                    }else{

                        Db::table('rp_user_growth_trend_agent')->insert([
                            'new_user_num'       => $result[0]['new_user_num'],
                            'jiazhuo_user_num'   => $result[0]['jiazhuo_user_num'],
                            'create_date'               => $v,
                            'agent_id'                  => $agent_id
                        ]);
                    }

                }
            }
        }
    }

    /*用户增长趋势 - 运营或超管维度*/
    public function user_growth_daily($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        //每日数据汇总
        $sql = "SELECT 
                    create_date,SUM(new_user_num) as new_user_num,SUM(jiazhuo_user_num) as jiazhuo_user_num
                FROM `rp_user_growth_trend_channel` 
                WHERE create_date = ?
                GROUP by create_date";

        foreach ($date_rang as $v) {

            $list   = Db::query($sql,[$v]);

            if($list){

                foreach ($list as $lv){

                    $record = Db::table('rp_user_growth_trend')->where(['create_date'=>$v])->field('id')->find();

                    if($record){

                        Db::table('rp_user_growth_trend')->where(['id'=>$record['id']])->update([
                            'new_user_num'       => $lv['new_user_num'],
                            'jiazhuo_user_num'   => $lv['jiazhuo_user_num'],
                        ]);

                    }else{

                        Db::table('rp_user_growth_trend')->insert([
                            'new_user_num'       => $lv['new_user_num'],
                            'jiazhuo_user_num'   => $lv['jiazhuo_user_num'],
                            'create_date'        => $v
                        ]);
                    }

                }

            }
        }
    }

    /*用户每日充值金额统计 - 投放号维度*/
    public function user_recharge_daily_channel($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        foreach ($date_rang as $date) {

            $list = Db::table('pay_order_success')
                ->where('status', 2)
                ->where('create_date', $date)
                ->field('SUM(total_fee) AS total_fee,channel_id,uid')->group('channel_id,uid')->select();

            if ($list) {
                foreach ($list as $lv) {

                    $record = Db::table('rp_user_recharge_amount_daily_channel')
                        ->where(['channel_id'=>$lv['channel_id'],'uid'=>$lv['uid'],'report_date'=>$date])->field('id')->find();

                    if($record){

                        Db::table('rp_user_recharge_amount_daily_channel')->where(['id'=>$record['id']])->update([
                            'money'                 => $lv['total_fee'],
                            'channel_id'            => $lv['channel_id'],
                            'uid'                   => $lv['uid']
                        ]);

                    }else{

                        Db::table('rp_user_recharge_amount_daily_channel')->insert([
                            'money'                 => $lv['total_fee'],
                            'channel_id'            => $lv['channel_id'],
                            'uid'                   => $lv['uid'],
                            'report_date'           => $date
                        ]);
                    }

                }
            }
        }
    }

    /*用户每日充值金额统计 - 代理维度*/
    public function user_recharge_daily_agent($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        $roleId = Db::table('cms_manager_role')->where('alias','agent')->value('id');
        $agents = Db::table('cms_manager_user')->where('role_id',$roleId)->column('id');

        foreach ($agents as $agent_id)
        {
            $channelIds = Db::table('cms_manager_user')->where('parent_id',$agent_id)->column('id');
            if(empty($channelIds))
            {
                continue;
            }

            $channelIdStr = implode(',',$channelIds);

            foreach ($date_rang as $v) {

                $sql = "SELECT 
                   report_date,uid,SUM(money) as money                 
                FROM `rp_user_recharge_amount_daily_channel` 
                WHERE report_date = '{$v}' AND channel_id in ({$channelIdStr})
                GROUP by report_date,uid";

                $list = Db::query($sql);

                if($list){

                    foreach ($list as $lv)
                    {
                        $record = Db::table('rp_user_recharge_amount_daily_agent')
                            ->where(['report_date'=>$v,'agent_id'=>$agent_id,'uid'=>$lv['uid']])
                            ->field('id')->find();

                        if($record){

                            Db::table('rp_user_recharge_amount_daily_agent')->where(['id'=>$record['id']])->update([
                                'money'              => $lv['money'],
                            ]);

                        }else{

                            Db::table('rp_user_recharge_amount_daily_agent')->insert([
                                'money'             => $lv['money'],
                                'uid'               => $lv['uid'],
                                'report_date'       => $v,
                                'agent_id'          => $agent_id
                            ]);
                        }
                    }

                }
            }
        }

    }

}